var knex = require('../dbhelper').instance();
var Promise = require('bluebird');
module.exports = {
    batchInsert: function (rows, resourceid) {
        return new Promise(function (resolve, reject) {
            var chunkSize = 10;
            knex.transaction(function (trx) {
                return Promise.all([
                        knex.batchInsert('fileresource', rows, chunkSize)
                        .returning('resourceid')
                        .transacting(trx),

                        knex('fileresource').update({
                            'flag': 1
                        }).where('resourceid', resourceid).transacting(trx)
                    ])
                    .then(trx.commit)
                    .catch(trx.rollback);
            }).then(function (data) {
                return resolve(data);
            }).catch(function (err) {
                console.error(err);
                reject(err);
            });
        });

        /* var chunkSize = 10;
        return new Promise(function (resolve, reject) {
            knex.transaction(function (tr) {
                return knex.batchInsert('fileresource', rows, chunkSize)
                    .returning('resourceid')
                    .transacting(tr)
            }).then(function (data) {
                resolve(data);
            }).catch(function (error) {
                console.error(error);
                reject(error);
            });
        }); */
    },
    getlist: function (condition) {
        return new Promise(function (resolve, reject) {
            knex.transaction(function (trx) {
                var promiseArray = [];
                //允许显示文件
                if (condition.showfile) {
                    if (typeof (condition.key) == 'string' || ((condition.key.resourcename == undefined || condition.key.resourcename.length === 0) && (condition.key.start === undefined || condition.key.start.length == 0) && (condition.key.end === undefined || condition.key.end.length == 0))) {
                        promiseArray.push(knex('fileresource').where('pid', condition.resourceid)
                            .select('*').limit(condition.limit).offset((condition.page - 1) * condition.limit).orderBy('createat', 'desc').transacting(trx));
                        promiseArray.push(knex('fileresource').where('pid', condition.resourceid)
                            .select(knex.raw('count(1) as total')).transacting(trx));
                    } else {
                        condition.key.start = condition.key.start || '2000-01-01 00:00:00';
                        condition.key.end = condition.key.end || '3000-01-01 00:00:00';
                        promiseArray.push(knex('fileresource').where(knex.raw('FIND_IN_SET(resourceid,getresource_childids(?))', [condition.resourceid])).andWhere('resourcename', 'like', '%' + condition.key.resourcename + '%').whereBetween('createat', [condition.key.start, condition.key.end])
                            .select('*').limit(condition.limit).offset((condition.page - 1) * condition.limit).orderBy('createat', 'desc').transacting(trx));

                        promiseArray.push(knex('fileresource').where(knex.raw('FIND_IN_SET(resourceid,getresource_childids(?))', [condition.resourceid])).andWhere('resourcename', 'like', '%' + condition.key.resourcename + '%').whereBetween('createat', [condition.key.start, condition.key.end])
                            .select(knex.raw('count(1) as total')).transacting(trx));
                    }
                } else { //不允许显示文件
                    if (typeof (condition.key) == 'string' || ((condition.key.resourcename == undefined || condition.key.resourcename.length === 0) && (condition.key.start === undefined || condition.key.start.length == 0) && (condition.key.end === undefined || condition.key.end.length == 0))) {
                        promiseArray.push(knex('fileresource').where('pid', condition.resourceid).andWhere('resourcetype', 0)
                            .select('*').limit(condition.limit).offset((condition.page - 1) * condition.limit).orderBy('createat', 'desc').transacting(trx));
                        promiseArray.push(knex('fileresource').where('pid', condition.resourceid).andWhere('resourcetype', 0)
                            .select(knex.raw('count(1) as total')).transacting(trx));
                    } else {
                        condition.key.start = condition.key.start || '2000-01-01 00:00:00';
                        condition.key.end = condition.key.end || '3000-01-01 00:00:00';
                        promiseArray.push(knex('fileresource').where(knex.raw('FIND_IN_SET(resourceid,getresource_childids(?))', [condition.resourceid])).andWhere('resourcetype', 0).andWhere('resourcename', 'like', '%' + condition.key.resourcename + '%').whereBetween('createat', [condition.key.start, condition.key.end])
                            .select('*').limit(condition.limit).offset((condition.page - 1) * condition.limit).orderBy('createat', 'desc').transacting(trx));

                        promiseArray.push(knex('fileresource').where(knex.raw('FIND_IN_SET(resourceid,getresource_childids(?))', [condition.resourceid])).andWhere('resourcetype', 0).andWhere('resourcename', 'like', '%' + condition.key.resourcename + '%').whereBetween('createat', [condition.key.start, condition.key.end])
                            .select(knex.raw('count(1) as total')).transacting(trx));
                    }
                }


                return Promise.all(promiseArray)
                    .then(trx.commit)
                    .catch(trx.rollback);
            }).then(function (resp) {
                resolve(resp);
            }).catch(function (err) {
                console.error(err);
            });
        });
    },
    bindfolders: function (params) {
        return new Promise(function (resolve, reject) {
            var tablename = 'dstable_' + params.dsid;
            // 事务模式
            knex.transaction(function (trx) {
                return Promise.all([
                        knex.raw('select getresource_childids(?) as ids', [params.folderids]).transacting(trx).then(function (data) {
                            var ids = data[0][0].ids.split(',');
                            return Promise.all([
                                knex.schema.hasTable(tablename).then(function (exists) {
                                    if (!exists) {
                                        return Promise.all([
                                            knex.raw('create table ' + tablename + '(resourceid varchar(32) primary key ,userid varchar(32),status int not null default 0,label json ,createat datetime not null default CURRENT_TIMESTAMP,updateat datetime not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,path varchar(1000) not null )ENGINE=MyISAM  DEFAULT CHARSET=utf8mb4').transacting(trx),
                                            knex.raw('insert into ??(resourceid,path) select resourceid,path from fileresource where pid in(select resourceid from fileresource where  resourceid in(' + ids.map(_ => '?').join(',') + ')) and resourcetype=1', [tablename, ...ids]).transacting(trx),
                                            knex('dslist').update({
                                                dstype: params.dstype,
                                                dstable: tablename,
                                                inputfolderids: new Array(params.folderids).join(','),
                                                totalcount: knex.raw('(select count(1) from ??)', [tablename])
                                            }).where('dsid', params.dsid).transacting(trx)
                                        ]);
                                    } else {
                                        return Promise.all([
                                            knex.raw('insert into ??(resourceid,path) select resourceid,path from fileresource where pid in(select resourceid from fileresource where  resourceid in(' + ids.map(_ => '?').join(',') + ')) and resourcetype=1', [tablename, ...ids]).transacting(trx),
                                            knex('dslist').update({
                                                dstype: params.dstype,
                                                dstable: tablename,
                                                inputfolderids: new Array(params.folderids).join(','),
                                                totalcount: knex.raw('(select count(1) from ??)', [tablename])
                                            }).where('dsid', params.dsid).transacting(trx)
                                        ]);
                                    }
                                })
                            ])
                        })
                    ])
                    .then(trx.commit)
                    .catch(trx.rollback);
            }).then(function (data) {
                return resolve(data);
            }).catch(function (err) {
                console.error(err);
                reject(err);
            });
        });
    },
    binddataset: function (params) {
        return new Promise(function (resolve, reject) {
            var tablename = 'dstable_' + params.dsid + '_merge';
            //var uniontables = params.ids.join(',');
            var currenttable = 'dstable_' + params.dsid;
            // 事务模式
            knex.transaction(function (trx) {
                return Promise.all([
                        /*  knex.raw('DROP table IF EXISTS ' + tablename).transacting(trx),
                         knex.raw('create table ' + tablename + '(resourceid varchar(32) primary key ,userid varchar(32),status int not null default 0,label json ,createat datetime not null default CURRENT_TIMESTAMP)ENGINE=MRG_MyISAM UNION=(' + uniontables + ') INSERT_METHOD=LAST CHARSET=utf8mb4;').transacting(trx), */

                        knex.raw('call proc_createdataset(?,?,?)', [currenttable, tablename, params.uniontables.join(',')]).transacting(trx),

                        knex('dslist').update({
                            dstype: params.dstype,
                            dstable: currenttable,
                            status: params.status,
                            inputdsids: params.ids.join(','),
                            totalcount: knex.raw('(select count(1) from ??)', [currenttable])
                        }).where('dsid', params.dsid).transacting(trx)
                    ])
                    .then(trx.commit)
                    .catch(trx.rollback);
            }).then(function (data) {
                return resolve(data);
            }).catch(function (err) {
                console.error(err);
                reject(err);
            });
        });
    },
    binddataset_export: function (params) {
        return new Promise(function (resolve, reject) {
            var query=[];
            params.uniontables.forEach(tablename => {
                query.push('select path,label from '+tablename);
            });
            // 事务模式
            knex.transaction(function (trx) {
                return Promise.all([
                        knex.raw(query.join(' union ')).transacting(trx)
                    ])
                    .then(trx.commit)
                    .catch(trx.rollback);
            }).then(function (data) {
                return resolve(data);
            }).catch(function (err) {
                console.error(err);
                reject(err);
            });
        });
    },
    binddataset_search: function (params) {
        return new Promise(function (resolve, reject) {
            var tablename = 'dstable_' + params.dsid;
            var sql = [];
            for (let i = 0; i < params.union_tables.length; i++) {
                const item = params.union_tables[i];
                sql.push('SELECT * FROM ' + item.table + ' where ' + item.where);
            }
            var sqlstring = 'insert into ' + tablename + ' select * from (' + sql.join(' union ') + ' ) as a';
            // 事务模式
            knex.transaction(function (trx) {
                return Promise.all([
                        knex.schema.dropTableIfExists(tablename).then(function (exists) {
                            return Promise.all([
                                knex.raw('create table ' + tablename + '(resourceid varchar(32) primary key ,userid varchar(32),status int not null default 0,label json ,createat datetime not null default CURRENT_TIMESTAMP,updateat datetime not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,path varchar(1000) not null )ENGINE=MyISAM  DEFAULT CHARSET=utf8mb4').transacting(trx),
                                knex.raw(sqlstring).transacting(trx),
                                knex('dslist').update({
                                    dstype: params.dstype,
                                    dstable: tablename,
                                    inputdsids: params.inputdsids.join(','),
                                    totalcount: knex.raw('(select count(1) from ??)', [tablename])
                                }).where('dsid', params.dsid).transacting(trx)
                            ]);
                        })

                    ])
                    .then(trx.commit)
                    .catch(trx.rollback);
            }).then(function (data) {
                return resolve(data);
            }).catch(function (err) {
                console.error(err);
                reject(err);
            });
        });
    },
    binddataset_search_export: function (params) {
        return new Promise(function (resolve, reject) {
            var tablename = 'dstable_' + params.dsid;
            var sql = [];
            for (let i = 0; i < params.union_tables.length; i++) {
                const item = params.union_tables[i];
                sql.push('SELECT * FROM ' + item.table + ' where ' + item.where);
            }
            var sqlstring = 'select * from (' + sql.join(' union ') + ' ) as a';
            // 事务模式
            knex.transaction(function (trx) {
                return Promise.all([
                        knex.schema.dropTableIfExists(tablename).then(function (exists) {
                            return Promise.all([
                                knex.raw(sqlstring).transacting(trx)
                            ]);
                        })

                    ])
                    .then(trx.commit)
                    .catch(trx.rollback);
            }).then(function (data) {
                return resolve(data);
            }).catch(function (err) {
                console.error(err);
                reject(err);
            });
        });
    },
    binddataset_showdetail: function (params) {
        return new Promise(function (resolve, reject) {
            var sql = [];
            for (let i = 0; i < params.union_tables.length; i++) {
                const item = params.union_tables[i];
                sql.push('SELECT * FROM ' + item.table + ' where ' + item.where);
            }
            var sqlstring = 'select count(1) totalcount from (' + sql.join(' union ') + ' ) as a';
            // 事务模式
            knex.transaction(function (trx) {
                return Promise.all([
                        knex.raw(sqlstring).transacting(trx)
                    ])
                    .then(trx.commit)
                    .catch(trx.rollback);
            }).then(function (data) {
                return resolve(data);
            }).catch(function (err) {
                console.error(err);
                reject(err);
            });
        });
    }
};